package com.example.sp.controller;


import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.sp.entity.SelectGet;
import com.example.sp.entity.StuInfo;
import com.example.sp.service.SelectGetService;
import com.example.sp.service.StuInfoService;
import com.example.sp.utils.Res;
import com.example.sp.utils.Result;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


@RestController
@RequestMapping("/stu")
@AllArgsConstructor
@Slf4j
  public class StuInfoController {
    private final StuInfoService stuInfoService;
    private final SelectGetService selectGetService;
    private final ResourceLoader resourceLoader;

    @GetMapping("/inf")
    public Result selectOne() {
        StuInfo st = stuInfoService.getOne(new LambdaQueryWrapper<StuInfo>().eq(StuInfo::getProvince, "山东"));
        return Res.ok(st);
    }
//    @GetMapping("/ins/{provinceNo}")
//    public Result selectTwo(@PathVariable("provinceNo")Long provinceNo){
//        StuInfo st=stuInfoService.getOne(new LambdaQueryWrapper<StuInfo>().eq(StuInfo::getProvinceNo,provinceNo));
//        return Res.ok(st);
//    }
    @GetMapping("/selectName")
    public Result selectName(StuInfo entity){
        return Res.ok(stuInfoService.listMaps(new LambdaQueryWrapper<StuInfo>()
                .like(StrUtil.isNotEmpty(entity.getStuName()),StuInfo::getStuName,entity.getStuName())));
    }

    /**
     *
     * @return 所有信息
     */
    @RequestMapping("/stuAll")
    public Result selectAll(String coll,String name){
        return Res.ok(stuInfoService.listMaps(new LambdaQueryWrapper<StuInfo>()
                .like(StrUtil.isNotEmpty(name),StuInfo::getStuName,name)
                .like(StrUtil.isNotEmpty(coll),StuInfo::getColleage,coll)));
    }

    /**
     * 省份 总人数 男生人数 女生人数
     *
     * @param
     * @return
     */
    @GetMapping("/shg/ab")
    public Result selectThree(Page<SelectGet> page) {
        return Res.ok(selectGetService.select1(page));
    }

    @GetMapping("/qdb")
    public Result selectFour(Page<StuInfo> page, StuInfo entity) {
        return Res.ok(stuInfoService.page(page, new LambdaQueryWrapper<StuInfo>()
                .like(StringUtils.isNotEmpty(entity.getProvince()), StuInfo::getProvince, entity.getProvince())
        ));
    }

    @GetMapping("/pro1")
    public List<Map<String, Object>> selectFive(String province) {
        List<Map<String, Object>> list;
        list = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("count(id) as num", "province",
                "count(if(stu_sex='女',true,null)) AS women", "count(if(stu_sex='男',true,null)) as man").eq("province", province));
        return list;
    }

    /**
     * 根据学院名 查询学院总人数 学院名 女生数 男生数
     *
     * @param colleage
     * @return
     */
    @GetMapping("/xy1")
    public Result selectSix(String colleage) {
        Map<String, Object> list2;
        list2 = stuInfoService.getMap(new QueryWrapper<StuInfo>().select("count(id) as num", "colleage",
                "count(if(stu_sex='女',true,null)) AS women",
                "count(if(stu_sex='男',true,null)) as man").eq("colleage", colleage));
        return Res.ok(list2);
    }

    @GetMapping("/nj1")
    public List<Map<String, Object>> selectSeven(String colleage, String grade) {
        List<Map<String, Object>> list3;
        list3 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("count(id) as num").eq("colleage", colleage).eq("grade", grade));
        return list3;
    }

    /**
     * 根据学院 返回每个年纪的人数
     *
     * @param colleage
     * @return
     */
    @GetMapping("/renshu")
    public Result selectEight(String colleage) {
        List<Map<String, Object>> list4;
        list4 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("colleage", "count(id) as num",
                "count(if(grade='2016级',true,null)) AS big4", "count(if(grade='2017级',true,null)) AS big3",
                "count(if(grade='2018级',true,null)) AS big2", "count(if(grade='2019级',true,null)) AS big1").eq("colleage", colleage)
        );
        return Res.ok(list4);
    }
    @GetMapping("/da")
    public Result selectaa(){
        return Res.ok(stuInfoService.list(new LambdaQueryWrapper<StuInfo>()));
    }

    /**
     *
     * @param colleage
     * @return 每个年级男女
     */
    @GetMapping("/xynv")
    public Result select11(String colleage) {
        List<Map<String, Object>> list4;
        list4 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("grade","colleage",
                "count(if(stu_sex='女',true,null)) AS women", "count(if(stu_sex='男',true,null)) AS man")
                .eq("colleage", colleage).groupBy("grade")
        );
        return Res.ok(list4);
    }
    /**
     *
     * @param province
     * @return 每个年级男女
     */
    @GetMapping("/prnv")
    public Result select12(String province) {
        List<Map<String, Object>> list4;
        list4 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("grade","province",
                "count(if(stu_sex='女',true,null)) AS women", "count(if(stu_sex='男',true,null)) AS man")
                .eq("province", province).groupBy("grade")
        );
        return Res.ok(list4);
    }
    /**
     *
     * @param profession
     * @return 每个系男女
     */
    @GetMapping("/xnv")
    public Result select14(String profession) {
        List<Map<String, Object>> list4;
        list4 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("profession",
                "count(if(stu_sex='女',true,null)) AS women", "count(if(stu_sex='男',true,null)) AS man")
                .eq("profession", profession)
        );
        return Res.ok(list4);
    }


    /**
     * 手机端
     *
     * @param province
     * @return
     */
    @GetMapping("/sheng")
    public Result selectE(String province) {
        List<Map<String, Object>> list11;
        list11 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("province", "count(id) as num",
                "count(if(stu_sex='男',true,null)) AS man",
                "count(if(stu_sex='女',true,null)) AS women").eq("province", province)
        );
        return Res.ok(list11);
    }


    /**
     * @return 各个学院 每一级人数
     */
    @GetMapping("/renshu1")
    public Result select10() {
        return Res.ok(stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("count(id) as num",
                "colleage",
                "count(if(grade='2016级',true,null)) AS big4",
                "count(if(grade='2017级',true,null)) AS big3",
                "count(if(grade='2018级',true,null)) AS big2",
                "count(if(grade='2019级',true,null)) AS big1").groupBy("colleage")));
    }
    /**
     *
     * @return 省份 贫困人数 全部人数
     */
    @GetMapping("/isp")
    public Result poor(){
        return Res.ok(stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("province",
                "count(if(ispoor='是',true,null)) as poorstu",
                ("count(id) as num")).groupBy("province")));

    }
    /**
     *
     * @return 省份 优秀人数 全部人数
     */
    @GetMapping("/stug")
    public Result goodStu(){
        return Res.ok(stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("province",
                "count(if(admitsorce>='540',true,null)) as goodstu",
                ("count(id) as num")).groupBy("province")));

    }
    @GetMapping("/xy2")

    public Result select6() {
        List<Map<String, Object>> list2;
        list2 = stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("count(id) as num", "colleage",
                "count(if(stu_sex='女',true,null)) AS women", "count(if(stu_sex='男',true,null)) as man").groupBy("colleage"));
        return Res.ok(list2);
    }

    /**
     * 插入一条
     *
     * @param entity
     * @return
     */
    @PostMapping("/save")
    public Result insert(@RequestBody StuInfo entity) {
        stuInfoService.save(entity);
        return Res.ok("添加成功！");
    }

    /**
     * 多条插入
     * @param entity
     * @return
     */
    @PostMapping("/saves")
    public Result insertSome(@RequestBody Collection<StuInfo> entity) {
        stuInfoService.saveBatch(entity);
        return Res.ok("添加成功！");
    }

    /**
     * 修改信息
     *
     * @param entity
     * @return
     */
    @PostMapping("/update")
    public Result update(@RequestBody StuInfo entity) {
        stuInfoService.updateById(entity);
        return Res.ok();
    }

    /**
     * 删除数据
     *
     * @param
     * @return 删除结果
     */
    @DeleteMapping("/delete/{id}")
    public Result delete(@PathVariable Long id) {
        stuInfoService.removeById(id);
        return Res.ok("删除成功！");
    }

    @GetMapping("/allcollege")
    public Result Allcollege() {
        return Res.ok(stuInfoService.listMaps(new QueryWrapper<StuInfo>().select("distinct colleage")));
    }

    /**
     *
     * @param file
     * @return 数据库导入Excel
     */
    @PostMapping("/sc")
    @ResponseBody
    public String importExcel(@RequestParam("file")MultipartFile file) {
        ImportParams params= new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        List<StuInfo> list;
        try {
            list= ExcelImportUtil.importExcel(file.getInputStream(),StuInfo.class,params);
            stuInfoService.saveBatch(list);
        }catch (Exception e){
            e.printStackTrace();
        }


//        try {
//            ExcelReader reader=ExcelUtil.getReader(file.getInputStream());
//            List<Map<String,Object>> readAll=reader.read(0,1,Integer.MAX_VALUE);
//            List<StuInfo> collect = readAll.stream().map(item -> BeanUtil.mapToBean(item, StuInfo.class, true)).collect(Collectors.toList());
//            stuInfoService.saveBatch(collect);
//            System.out.println(readAll);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
        return "OK";
    }
    @GetMapping("/temp")
    public void temp(HttpServletResponse response){

        String path = "statis/template.xlsx";

        Resource resource =  resourceLoader.getResource("classpath:"+path);

        response.setContentType("application/vnd.ms-excel");
        response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.addHeader("charset", "utf-8");
        response.addHeader("Pragma", "no-cache");
        response.setHeader("Content-Disposition", "attachment; filename=template.xlsx");


        try {
            IoUtil.copy(resource.getInputStream(),response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }


    }
//    @GetMapping("/export")
//    public void export(HttpServletResponse response) {
////        模拟从数据库获取需要导出的数据
//        List<Map<String,Object>> list =stuInfoService.listMaps(new LambdaQueryWrapper<StuInfo>().eq(StuInfo::getId,"1"));
////         导出操作
//           ExcelUtils.exportExcel(list, "测试", "sheet1", StuInfo.class, "file2.xlsx", response);
//
//    }
}
